/** (c) 2005-2007 Messaging Logic, All Rights Reserved. */
package org.xi8ix.jdbc;

import javax.sql.DataSource;
import java.sql.*;
import java.util.Calendar;
import java.util.Collection;
import java.util.TimeZone;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * A simplified API to access JDBC databases. Convenience methods makes easy JDBC calls
 * easy without placing abstractions over the underlying JDBC API.
 *
 * @author Iain Shigeoka
 */
public class SimpleStatement {
    /** Class logger. */
    private Logger LOG;
    /** Key for property prefix */
    public static String PROPERTY_PREFIX_KEY = SimpleStatement.class.getName()+".propertyPrefix";
    /** The UTC timezone calendar. */
    private static Calendar UTC_CALENDAR = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
    /** The data source the statement will use. */
    private DataSource dataSource;
    /** Flag indicating if exceptions should be thrown during queries and updates. */
    private boolean throwsRuntimeException;
    /** Flag indicating if dates should force the UTC time zone. */
    private boolean forceUTC;

    /**
     * Create a simple statement object that uses the provided data source. By default,
     * the simple statement will NOT throw runtime exceptions during queries or updates.
     *
     * @param dataSource the data source the statement will use
     */
    public SimpleStatement(DataSource dataSource) {
        this(dataSource,false,false);
    }

    /**
     * Create a simple statement object that uses the provided data source.
     *
     * @param dataSource the data source the statement will use
     * @param throwsRuntimeException true if SimpleStatementException is thrown when errors occur during queries or updates.
     * @param forceUTC true if UTC timezone should be fornced for all date parameters
     */
    public SimpleStatement(DataSource dataSource, boolean throwsRuntimeException, boolean forceUTC) {
        this.dataSource = dataSource;
        this.throwsRuntimeException = throwsRuntimeException;
        this.forceUTC = forceUTC;
        LOG = Logger.getLogger(System.getProperty(PROPERTY_PREFIX_KEY,"org.xi8ix.jdbc")+".SimpleStatement");
        if (LOG.isLoggable(Level.FINEST)) {
            LOG.log(Level.FINEST,"Configured with data source " + dataSource);
        }
    }

    /**
     * Returns true if queries and updates should throw a SimpleStatementException. If false,
     * no exceptions are thrown when errors occur.
     *
     * @return true if SimpleStatementException is thrown when errors occur during queries or updates.
     */
    public boolean isThrowsRuntimeException() {
        return throwsRuntimeException;
    }

    /**
     * Set to true if queries and updates should throw a SimpleStatementException if problems occur.
     *
     * @param throwsRuntimeException true if queries and updates should throw SimpleStatementException
     */
    public void setThrowsRuntimeException(boolean throwsRuntimeException) {
        this.throwsRuntimeException = throwsRuntimeException;
    }

    /**
     * Returns true if all date and timestamp parameters should be forced to use the UTC time zone. By
     * default this is false and the default timezone for the JVM is used.
     *
     * @return true if UTC timezone should be forced on all date and timestamp fields
     */
    public boolean isForceUTC() {
        return forceUTC;
    }

    /**
     * Sets the flag indicating if the UTC timezone should be forced.
     *
     * @param forceUTC true if UTC timezone should be forced
     */
    public void setForceUTC(boolean forceUTC) {
        this.forceUTC = forceUTC;
    }

    /**
     * Execute a standard JDBC call with the provided parameters and types and returns the first matching row
     * as an object. If more than one row matches, just the first row is returned.
     *
     * @param mapper the result row mapper to transform result set rows to objects
     * @param sql the SQL statement to execute
     * @param params an array of parameter objects to be set on the prepared statement (or null for no parameters)
     * @return the matching object found, or null if no matching objects found
     */
    public <T> T query(ResultMapper<T> mapper, String sql, Object... params) {
        T item = null;
        Connection conn = null;
        PreparedStatement ps = null;
        Exception exception = null;

        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement(sql);

            setupParameters(sql, ps, params);

            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                item = mapper.mapRow(rs,0);
            }

        } catch (SQLException e) {
            LOG.log(Level.WARNING,"SQL",e); exception = e;
        } finally {
            try { if (ps != null) { ps.close(); }
            } catch (Exception e) { LOG.log(Level.WARNING,"SQL",e); if (exception == null) { exception = e; } }
            try { if (conn != null) { conn.close(); }
            } catch (Exception e) { LOG.log(Level.WARNING,"SQL",e); if (exception == null) { exception = e; } }
        }
        if (throwsRuntimeException && exception != null) {
            throw new SimpleStatementException(exception);
        }
        return item;
    }

    /**
     * Execute a standard JDBC call with the provided parameters and types.
     * See complete query for list usage.
     *
     * @param mapper the result row mapper to transform result set rows to objects
     * @param list the list to add the results to
     * @param sql the SQL statement to execute
     * @param params an array of parameter objects to be set on the prepared statement (or null for no parameters)
     */
    public <T> void query(ResultMapper<T> mapper, Collection<T> list, String sql, Object... params) {
        query(mapper,list,null,-1,sql,params);
    }

    /**
     * Execute a standard JDBC call with the provided parameters and types.
     * See complete query for list usage.
     *
     * @param mapper the result row mapper to transform result set rows to objects
     * @param list the list to add the results to
     * @param limit the maximum number of results to return or -1 to return an unlimited number of rows
     * @param sql the SQL statement to execute
     * @param params an array of parameter objects to be set on the prepared statement (or null for no parameters)
     */
    public <T> void query(ResultMapper<T> mapper, Collection<T> list, int limit, String sql, Object... params) {
        query(mapper,list,null,limit,sql,params);
    }

    /**
     * Execute a standard JDBC call with the provided parameters and types.
     * See complete query for list usage.
     *
     * @param mapper the result row mapper to transform result set rows to objects
     * @param list the list to add the results to
     * @param fieldNames a list to receive the names of the fields in the result set
     * @param sql the SQL statement to execute
     * @param params an array of parameter objects to be set on the prepared statement (or null for no parameters)
     */
    public <T> void query(ResultMapper<T> mapper, Collection<T> list, Collection<String> fieldNames, String sql, Object... params) {
        query(mapper,list,fieldNames,-1,sql,params);
    }

    /**
     * Execute a standard JDBC call with the provided parameters and types.
     * Note: for streaming data, an abstract list implementation must only implement
     * the list.add(E) method to be used to receive data results as they are pulled
     * from the database. The list.add(E) method is called for each row in order within
     * the context of the error checking and recovery of the SimpleStatement query so
     * simply throwing an exception can stop data from being pulled and automatically closes
     * the database resources.
     *
     * @param mapper the result row mapper to transform result set rows to objects
     * @param list the list to add the results to
     * @param fieldNames a list to receive the names of the fields in the result set
     * @param limit the maximum number of results to return or -1 to return an unlimited number of rows
     * @param sql the SQL statement to execute
     * @param params an array of parameter objects to be set on the prepared statement (or null for no parameters)
     */
    public <T> void query(ResultMapper<T> mapper, Collection<T> list, Collection<String> fieldNames, int limit, String sql, Object... params) {
        Connection conn = null;
        PreparedStatement ps = null;

        Exception exception = null;
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement(sql);

            setupParameters(sql, ps, params);

            ResultSet rs = ps.executeQuery();
            if (fieldNames != null) {
                ResultSetMetaData meta = rs.getMetaData();
                for (int column = 1; column <= meta.getColumnCount(); column++) {
                    fieldNames.add(meta.getColumnName(column));
                }
            }
            if (limit <= 0) {
                limit = Integer.MAX_VALUE;
            }
            if (list == null) {
                for (int row = 0; rs.next() && row < limit; row++) {
                    mapper.mapRow(rs,row);
                }
            } else {
                for (int row = 0; rs.next() && row < limit; row++) {
                    T t = mapper.mapRow(rs,row);
                    if (t != null) {
                        list.add(t);
                    }
                }
            }

        } catch (SQLException e) {
            LOG.log(Level.WARNING,"SQL",e); exception = e;
        } finally {
            try { if (ps != null) { ps.close(); }
            } catch (Exception e) { LOG.log(Level.WARNING,"SQL",e); if (exception == null) { exception = e; } }
            try { if (conn != null) { conn.close(); }
            } catch (Exception e) { LOG.log(Level.WARNING,"SQL",e); if (exception == null) { exception = e; } }
        }
        if (throwsRuntimeException && exception != null) {
            throw new SimpleStatementException(exception);
        }
    }

    /**
     * Execute a standard JDBC update with the provided parameters and types.
     *
     * @param sql the SQL statement to execute
     * @param params an array of parameter objects to be set on the prepared statement (or null for no parameters)
     * @return either (1) the row count for INSERT, UPDATE, or DELETE statements
     *      (2) 0 for SQL statements that return nothing, or (3) a negative number set to the SQL error code if
     *      an exception occurred and throwsRuntimeException is false
     */
    public int execute(String sql, Object... params) {
        Connection conn = null;
        PreparedStatement ps = null;
        Exception exception = null;

        int result = -1;

        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement(sql);

            setupParameters(sql, ps, params);

            result = ps.executeUpdate();

            if (LOG.isLoggable(Level.FINEST)) {
                LOG.log(Level.FINEST,"Update result " + result);
            }

        } catch (SQLException e) {
            LOG.log(Level.WARNING,"SQL",e); result = e.getErrorCode(); if (result > 0) { result *= -1; } exception = e;
        } finally {
            try { if (ps != null) { ps.close(); }
            } catch (Exception e) { LOG.log(Level.WARNING,"SQL",e); if (exception == null) { exception = e; } }
            try { if (conn != null) { conn.close(); }
            } catch (Exception e) { LOG.log(Level.WARNING,"SQL",e); if (exception == null) { exception = e; } }
        }
        if (throwsRuntimeException && exception != null) {
            throw new SimpleStatementException(exception);
        }
        return result;
    }

    /**
     * Setup a prepared statement with the provided parameters and parameter types.
     *
     * @param sql the SQL used in this statement (used for debugging)
     * @param ps the prepared statement being setup
     * @param params an array of parameter objects to be set on the prepared statement (or null for no parameters)
     * @throws SQLException if there was a problem setting up the parameters
     */
    private void setupParameters(String sql, PreparedStatement ps, Object[] params) throws SQLException {
        StringBuffer info = null;
        if (LOG.isLoggable(Level.FINEST)) {
            info = new StringBuffer(sql);
            if (params != null && params.length > 0) {
                info.append("\n");
            }
        }
        if (params != null) {
            for (int i = 0; i < params.length; i++) {
                if (params[i] instanceof SimpleParameter) {
                    SimpleParameter param = (SimpleParameter) params[i];
                    if (forceUTC) {
                        switch (param.getType()) {
                            case Types.DATE:
                                if (param.getValue() == null) {
                                    ps.setNull(i+1,Types.DATE);
                                } else {
                                    ps.setDate(i+1,(Date)param.getValue(),UTC_CALENDAR);
                                }
                                break;
                            case Types.TIMESTAMP:
                                if (param.getValue() == null) {
                                    ps.setNull(i+1,Types.TIMESTAMP);
                                } else {
                                    ps.setTimestamp(i+1,(Timestamp)param.getValue(),UTC_CALENDAR);
                                }
                                break;
                            case Types.TIME:
                                if (param.getValue() == null) {
                                    ps.setNull(i+1,Types.TIME);
                                } else {
                                    ps.setTime(i+1,(Time)param.getValue(),UTC_CALENDAR);
                                }
                                break;
                            default:
                                ps.setObject(i+1,param.getValue(),param.getType());
                        }
                    } else {
                        ps.setObject(i+1,param.getValue(),param.getType());
                    }
                    if (LOG.isLoggable(Level.FINEST) && info != null) {
                        info.append(" [").append(param.getType()).append("]").append(param.getValue());
                    }
                } else {
                    if (forceUTC) {
                        if (params[i] instanceof Date) {
                            ps.setDate(i+1,(Date)params[i],UTC_CALENDAR);
                        } else if (params[i] instanceof Timestamp) {
                            ps.setTimestamp(i+1,(Timestamp)params[i],UTC_CALENDAR);
                        } else if (params[i] instanceof Time) {
                            ps.setTime(i+1,(Time)params[i],UTC_CALENDAR);
                        } else {
                            ps.setObject(i+1,params[i]);
                        }
                    } else {
                        ps.setObject(i+1,params[i]);
                    }
                    if (LOG.isLoggable(Level.FINEST) && info != null) {
                        info.append(" [] ").append(params[i]);
                    }
                }
            }
        }
        if (LOG.isLoggable(Level.FINEST) && info != null) {
            LOG.finest(info.toString());
        }
    }
}
